package br.ufrj.neg.evento.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import br.ufrj.neg.bean.Idioma;
import br.ufrj.neg.contatos.bean.ContatoBean;
import br.ufrj.neg.evento.bean.ArtefatoBean;
import br.ufrj.neg.evento.bean.EventoBean;
import br.ufrj.neg.model.ServiceLocator;
import br.ufrj.neg.util.Data;

public class EventoModel {
	
	
	public static int insereEvento(EventoBean evento) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "INSERT INTO evento(even_cd_id,nego_cd_id,even_dt_data_inicio, even_dt_data_fim," +
					"even_ds_titulo,tiev_cd_id,even_tx_descricao,even_ds_local,even_tx_memoria) values(DEFAULT,?, ?, ?, ?, ?,?,?,?) RETURNING even_cd_id";

			ps = conn.prepareStatement(sql);
			ps.setInt(1, evento.getIdNegociacao());
			ps.setTimestamp(2, new java.sql.Timestamp(evento.getDataInicio().getSQLData().getTime()));
			ps.setTimestamp(3, new java.sql.Timestamp(evento.getDataFim().getSQLData().getTime()));
			ps.setString(4, evento.getTitulo());
			ps.setInt(5, evento.getTipoEvento());
			ps.setString(6, evento.getDescricao());
			ps.setString(7, evento.getLocal());
			//ps.setDouble(8, evento.getCusto());
			ps.setString(8, evento.getMemoria());
			
			rs = ps.executeQuery();
			rs.next();
			int id = -1;
			id = rs.getInt(1);
			evento.setId(id);
							
			//Inserir Contatos.
			List<ContatoBean> contatos = evento.getParticipantes();
			
			if(!contatos.isEmpty()){
				
				
				//Monta Query
				sql = "INSERT INTO evento_contatos(even_cd_id,cont_cd_id) VALUES ";
				Iterator<ContatoBean> it = contatos.iterator();
				while(it.hasNext()){
					
					sql +="(?,?)";
					it.next();
					if(it.hasNext()){
						sql+=",";
					}
					
				}
				
				ps = conn.prepareStatement(sql);
				
				it = contatos.iterator();
				Integer n = 1;
				while(it.hasNext()){
					
					ps.setInt((2*n)-1, evento.getId());
					ps.setInt(2*n, it.next().getIdContato());
					n++;
				}
				
				ps.executeUpdate();
			
			}
			conn.commit();
			
			

			return evento.getId();
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	
	public static boolean insereContatoEvento(Integer idEvento,Integer idContato) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			
			String sql = "SELECT ec.evco_cd_valor_hora as valor_hora FROM evento_contatos ec INNER JOIN evento e ON ec.even_cd_id = e.even_cd_id WHERE cont_cd_id = ? ORDER BY even_dt_data_fim DESC LIMIT 1";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idContato);
			rs = ps.executeQuery();
			
			Double valorHora = 0.0;
			if(rs.next()){
				
				valorHora = rs.getDouble("valor_hora");
			}
			
			sql = "INSERT INTO evento_contatos(even_cd_id,cont_cd_id,evco_cd_valor_hora) VALUES (?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);
			ps.setInt(2, idContato);
			ps.setDouble(3, valorHora);
			ps.executeUpdate();
			
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}
	
	public static boolean removeContatoEvento(Integer idEvento,Integer idContato) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			
			String sql = "DELETE FROM evento_contatos WHERE even_cd_id=? AND cont_cd_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);
			ps.setInt(2, idContato);
			ps.executeUpdate();
			
			conn.commit();

			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public static boolean atualizaEvento(EventoBean evento) {
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "UPDATE evento SET nego_cd_id = ?,even_dt_data_inicio=?,even_dt_data_fim=?,even_ds_titulo=?,tiev_cd_id=?,even_tx_descricao=?,even_ds_local=?,even_tx_memoria=?" +
					" WHERE even_cd_id=?";
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, evento.getIdNegociacao());
			ps.setTimestamp(2, new java.sql.Timestamp(evento.getDataInicio().getSQLData().getTime()));
			ps.setTimestamp(3, new java.sql.Timestamp(evento.getDataFim().getSQLData().getTime()));
			ps.setString(4, evento.getTitulo());
			ps.setInt(5, evento.getTipoEvento());
			ps.setString(6, evento.getDescricao());
			ps.setString(7, evento.getLocal());
			//ps.setDouble(8, evento.getCusto());
			ps.setString(8, evento.getMemoria());
			ps.setInt(9, evento.getId());
	
			ps.executeUpdate();
					
			conn.commit();
						
			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	public static EventoBean getEvento(int idEvento,Idioma idioma) {
		ResultSet rs = null;
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "SELECT  * FROM evento e INNER JOIN tipo_evento t ON e.tiev_cd_id = t.tiev_cd_id WHERE even_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);
			rs = ps.executeQuery();
			EventoBean evento = null;
			while (rs.next()) {
				
				evento = new EventoBean();
				evento.setIdNegociacao(rs.getInt("nego_cd_id"));
				evento.setDataInicio(new Data(rs.getTimestamp("even_dt_data_inicio")));
				evento.setDtInicio(evento.getDataInicio().getDataAAAAMMDD());
				evento.setDataFim(new Data(rs.getTimestamp("even_dt_data_fim")));
				evento.setDtFim(evento.getDataFim().getDataAAAAMMDD());
				evento.setHoraFim(evento.getHora("fim"));
				evento.setHoraInicio(evento.getHora("inicio"));
				evento.setTitulo(rs.getString("even_ds_titulo"));
				evento.setTipoEvento(rs.getInt("tiev_cd_id"));
				evento.setDescricao(rs.getString("even_tx_descricao"));
				evento.setLocal(rs.getString("even_ds_local"));
				evento.setId(rs.getInt("even_cd_id"));
				//evento.setCusto(rs.getDouble("even_cd_custo")); //FIXME: _getCustos
				evento.setMemoria(rs.getString("even_tx_memoria"));
				evento.setTipoEventoNome(rs.getString("tiev_ds_titulo"));
				
				sql = "SELECT SUM(soma) as resultado FROM ((SELECT SUM(evco_cd_deslocamento) as soma,1 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(evco_cd_valor_hora)as soma,2 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(evco_cd_hospedagem)as soma,3 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(cuge_cd_valor)as soma,4 FROM custos_gerais WHERE even_cd_id = ?)) x";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				ps.setInt(2, evento.getId());
				ps.setInt(3, evento.getId());
				ps.setInt(4, evento.getId());
				
				ResultSet rs2 = ps.executeQuery();
				
				if(rs2.next())	evento.setCusto(rs2.getDouble("resultado"));
			    else evento.setCusto(0.0); 
				
				
				sql = "SELECT  * FROM contatos c INNER JOIN evento_contatos e ON e.cont_cd_id = c.cont_cd_id WHERE e.even_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				rs2 = ps.executeQuery();
				
				List<ContatoBean> contatos = new ArrayList<ContatoBean>();
				ContatoBean contato = null;
				while (rs2.next()) {
					
					contato = new ContatoBean();
					contato.setIdContato(rs2.getInt("cont_cd_id"));
					contato.setEmail(rs2.getString("cont_ds_email"));
					contato.setNome(rs2.getString("cont_ds_nome"));
					contato.setCargo(rs2.getString("cont_ds_cargo"));
					contato.setEndereco(rs2.getString("cont_ds_endereco"));
					contato.setTelefone1(rs2.getString("cont_ds_telefone1"));
					contato.setTelefone2(rs2.getString("cont_ds_telefone2"));
			
					contatos.add(contato);
				}
				evento.setParticipantes(contatos);
				
				sql = "SELECT  * FROM artefato a WHERE a.even_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				rs2 = ps.executeQuery();
				
				List<ArtefatoBean> artefatos = new ArrayList<ArtefatoBean>();
				ArtefatoBean artefato = null;
				while (rs2.next()) {
					
					artefato = new ArtefatoBean(idioma);
					artefato.setId(rs2.getInt("arte_cd_id"));
					artefato.setNome(rs2.getString("arte_ds_nome"));
					artefato.setDescricao(rs2.getString("arte_tx_descricao"));
					artefato.setDataFormatada(new Data(rs2.getDate("arte_dt_data")).getDataAAAAMMDD());
					artefatos.add(artefato);
				}
				evento.setArtefatos(artefatos);
			}
		
						
			//Pegar lista de artefatos
			
			/*
			sql = "SELECT * FROM artefato WHERE even_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, evento.getId());
			rs = ps.executeQuery();
		
			while (rs.next()) {
				
				evento.getParticipantes().(rs.getString("tiev_ds_nome"));
			}
			*/
			//Pegar lista de participantes
			
			
			return evento;
		} catch (Exception e) {
			
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	/**
	 * @param idNegocio
	 * @param trb - Rever questão da tradução.
	 * @return
	 */
	public static Collection<EventoBean> getEventosPorNegociacao (int idNegocio) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "SELECT  * FROM evento e INNER JOIN tipo_evento t ON e.tiev_cd_id = t.tiev_cd_id WHERE nego_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegocio);
			rs = ps.executeQuery();
			
			Collection<EventoBean> eventos = new ArrayList<EventoBean>();
			EventoBean evento;
			//NegociacaoElementoModel nem = new NegociacaoElementoModel();
			//TipoReuniaoModel trm = new TipoReuniaoModel();
			
			while (rs.next()) {
									
				evento = new EventoBean();
				evento.setIdNegociacao(rs.getInt("nego_cd_id"));
				evento.setDataInicio(new Data(rs.getTimestamp("even_dt_data_inicio")));
				evento.setDtInicio(evento.getDataInicio().getDataAAAAMMDD());
				evento.setDataFim(new Data(rs.getTimestamp("even_dt_data_fim")));
				evento.setDtFim(evento.getDataFim().getDataAAAAMMDD());
				evento.setHoraFim(evento.getHora("fim"));
				evento.setHoraInicio(evento.getHora("inicio"));
				evento.setTitulo(rs.getString("even_ds_titulo"));
				evento.setTipoEvento(rs.getInt("tiev_cd_id"));
				evento.setDescricao(rs.getString("even_tx_descricao"));
				evento.setLocal(rs.getString("even_ds_local"));
				evento.setId(rs.getInt("even_cd_id"));
				evento.setCusto(rs.getDouble("even_cd_custo"));
				evento.setMemoria(rs.getString("even_tx_memoria"));
				evento.setTipoEventoNome(rs.getString("tiev_ds_titulo"));
				
				sql = "SELECT SUM(soma) as resultado FROM ((SELECT SUM(evco_cd_deslocamento) as soma,1 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(evco_cd_valor_hora)as soma,2 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(evco_cd_hospedagem)as soma,3 FROM evento_contatos WHERE even_cd_id = ? ) " +
						"UNION (SELECT SUM(cuge_cd_valor)as soma,4 FROM custos_gerais WHERE even_cd_id = ?)) x";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				ps.setInt(2, evento.getId());
				ps.setInt(3, evento.getId());
				ps.setInt(4, evento.getId());
				
				ResultSet rs2 = ps.executeQuery();
				
				if(rs2.next())	evento.setCusto(rs2.getDouble("resultado"));
			    else evento.setCusto(0.0);

				sql = "SELECT  * FROM contatos c INNER JOIN evento_contatos e ON e.cont_cd_id = c.cont_cd_id WHERE e.even_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				rs2 = ps.executeQuery();
				
				List<ContatoBean> contatos = new ArrayList<ContatoBean>();
				ContatoBean contato = null;
				while (rs2.next()) {
					
					contato = new ContatoBean();
					contato.setIdContato(rs2.getInt("cont_cd_id"));
					contato.setEmail(rs2.getString("cont_ds_email"));
					contato.setNome(rs2.getString("cont_ds_nome"));
			
					contatos.add(contato);
				}
				evento.setParticipantes(contatos);
				
				//Pegar lista de artefatos

				/*
				sql = "SELECT * FROM artefato WHERE even_cd_id = ?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, evento.getId());
				rs = ps.executeQuery();

				while (rs.next()) {
	
					evento.getParticipantes().(rs.getString("tiev_ds_nome"));
				}
				 */
				//Pegar lista de participantes
				eventos.add(evento);
			}
			return eventos;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}

	/**
	 * 
	 * @return Collection<String> contendo os modelos cadastrados na ordem que foram cadastrados
	 */
	public static Collection<String> getModelos () {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "SELECT  * FROM modelo_memoria_evento mme";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			Collection<String> modelos = new ArrayList<String>();
			String modelo = "";
			//NegociacaoElementoModel nem = new NegociacaoElementoModel();
			//TipoReuniaoModel trm = new TipoReuniaoModel();
			
			while (rs.next()) {
									
				modelo = rs.getString("mome_tx_texto");
				modelos.add(modelo);
			}
			return modelos;
		} catch (Exception e) {
			System.out.println(e);
			return null;
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}
	}	
	
	
	public static boolean removeEvento(int idEvento) {	
		PreparedStatement ps = null;
		Connection conn = ServiceLocator.getConnection();
		try {				
			/* Remove a reuniao */
			String sql = "DELETE FROM evento where even_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);
			System.out.println(sql);
			ps.executeUpdate();

			conn.commit();

			return true;
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				ps.close();
			} catch (Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (Exception e) {
				System.out.println(e);
			}
		}

	}

	public static boolean checkNegociacao(int idNegociacao, Integer idUsuario) {
		
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = ServiceLocator.getConnection();
		try {
			String sql = "SELECT * FROM negociacao WHERE nego_cd_id = ? AND usua_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idNegociacao);
			ps.setInt(2, idUsuario);
			rs = ps.executeQuery();
				
			if (rs.next()) {
									
				return true;
			}
			return false;
			
		} catch (Exception e) {
			System.out.println(e);
			return false;
		} finally {
			try {
				rs.close();
			} catch (final Exception e) {
				System.out.println(e);
				
			}
			try {
				ps.close();
			} catch (final Exception e) {
				System.out.println(e);
			}
			try {
				conn.close();
			} catch (final Exception e) {
				System.out.println(e);
			}
			
		}
		
	}
}
